package com.cntcdt.api.db.dao;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

import com.cntcdt.api.db.dao.base.ConsociationCompanyDayCntDaoBase;
import com.cntcdt.api.db.model.ConsociationCompanyDayCntModel;
import com.cntcdt.api.db.model.ConsociationCompanyDaySumModel;

/**
 * 这是cdt_consociation_company_day_cnt的Dao实现类
 */
public class ConsociationCompanyDayCntDao extends ConsociationCompanyDayCntDaoBase   {
    /**
     * ConsociationCompanyDayCntDao对象
     */
    private static ConsociationCompanyDayCntDao instance = new ConsociationCompanyDayCntDao();

    /**
     * 默认构造方法
     */
    private ConsociationCompanyDayCntDao() {
    }

    /**
     * 获得对象实例
     */
    public static ConsociationCompanyDayCntDao getInstance() { 
         return instance;
    } 
    
    /**
     * 按昨天的集团数据
     * @param id id
     * @return model
     */
    public ConsociationCompanyDayCntModel findByYesterday(String consociationCompanyId,int shopId) {
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -1);            
        String  yestedayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        String sql = "SELECT * FROM " + TABLE + " WHERE addTime = ? and consociationCompanyId in ("+consociationCompanyId+") and shopId = ? order by addTime";
       Object[] paramObjs = {yestedayDate,shopId};
       List<ConsociationCompanyDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       if(modelList.size() > 0){
    	   return modelList.get(0);
       }
       return null;
   }  
    
    /**
     * 查出本月的集团数据
     * @param id id
     * @return model
     */
    public List<ConsociationCompanyDayCntModel> findByThisMonth(String consociationCompanyId,int shopId) {
        Calendar cal_2 = Calendar.getInstance();    
        cal_2.add(Calendar.MONTH, 0);
        cal_2.set(Calendar.DAY_OF_MONTH,1);//设置为1号,当前日期既为本月第一天 
        String firstDay = new SimpleDateFormat("yyyy-MM-dd").format(cal_2.getTime());
    	
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 0);            
        String  todayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        System.out.println(firstDay);
        System.out.println(todayDate);
        String sql = "SELECT * FROM " + TABLE + " WHERE addTime >= ? and addTime <= ? and consociationCompanyId in ("+consociationCompanyId+")  and shopId = ? order by addTime";
       Object[] paramObjs = {firstDay,todayDate,shopId};
       List<ConsociationCompanyDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   } 
    
    /**
     * 查出近7天的集团数据
     * @param id id
     * @return model
     */
    public List<ConsociationCompanyDayCntModel> findByLastSevenDayData(String consociationCompanyId,int shopId) {
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -1);            
        String  yesterdayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        
		Calendar calendar2 = Calendar.getInstance();
		calendar2.add(Calendar.DATE, -7);            
        String  lastSevenDayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar2.getTime());
        
        String sql = "SELECT id,terminal,name,address,SUM(borrowUserSum) as borrowUserSum,SUM(borrowCDBSum) as borrowCDBSum,SUM(newUserSum) as newUserSum,SUM(borrowRentSum) as borrowRentSum,SUM(sellLineMoneySum) as sellLineMoneySum,SUM(sellCDBSum) as sellCDBSum,addTime,updateTime,consociationCompanyId,sortTime,SUM(sellCDBSum) as sellCDBSum,SUM(sellLineNum) as sellLineNum,SUM(sellLineMoneySum2) as sellLineMoneySum2,SUM(borrowUserGross) as borrowUserGross,SUM(borrowGross) as borrowGross,SUM(sellCDBNum) as sellCDBNum,SUM(sellCDBGross) as sellCDBGross,SUM(divideBorrowRentSum) as divideBorrowRentSum,SUM(divideSellLineMoneySum) as divideSellLineMoneySum,SUM(divideSellCDBSum) as divideSellCDBSum FROM " + TABLE + " WHERE addTime >= ? and addTime <= ? and consociationCompanyId in ("+consociationCompanyId+")  and shopId = ? GROUP BY addTime";
       Object[] paramObjs = {lastSevenDayDate,yesterdayDate,shopId};
       List<ConsociationCompanyDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   } 
    
    /**
     * 分页查询
     * @param id id
     * @return model
     */
    public List<ConsociationCompanyDayCntModel> findBySortTime(String consociationCompanyId,int shopId, long sortTime) {
        String sql = "SELECT * FROM " + TABLE + " WHERE consociationCompanyId in ("+consociationCompanyId+")  and shopId = ? and sortTime < ? order by sortTime desc limit 20";
       Object[] paramObjs = {shopId,sortTime};
       List<ConsociationCompanyDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   } 
    
    /**
     * 按昨天的集团数据（所有设备）
     * @param id id
     * @return model
     */
    public List<ConsociationCompanyDayCntModel> findByYesterday2(String consociationCompanyId,String dataTime) {
        String sql = "SELECT * FROM " + TABLE + " WHERE addTime = ? and consociationCompanyId in ("+consociationCompanyId+") order by addTime";
       Object[] paramObjs = {dataTime};
       List<ConsociationCompanyDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   }  
    
    /**
     * 查出本月的集团数据部分设备
     * @param id id
     * @return model
     */
    public List<ConsociationCompanyDayCntModel> findByThisMonth2(String consociationCompanyId) {
        Calendar cal_2 = Calendar.getInstance();    
        cal_2.add(Calendar.MONTH, 0);
        cal_2.set(Calendar.DAY_OF_MONTH,1);//设置为1号,当前日期既为本月第一天 
        String firstDay = new SimpleDateFormat("yyyy-MM-dd").format(cal_2.getTime());
    	
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 0);            
        String  todayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        System.out.println(firstDay);
        System.out.println(todayDate);
        String sql = "SELECT * FROM " + TABLE + " WHERE addTime >= ? and addTime <= ? and consociationCompanyId in ("+consociationCompanyId+") order by addTime";
       Object[] paramObjs = {firstDay,todayDate};
       List<ConsociationCompanyDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   } 
    
    /**
     * 根据天数查询数据
     * @param id id
     * @return model
     */
    public List<ConsociationCompanyDayCntModel> findByDayNum(Integer dayNum,String terminal) {
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -1);            
        String  yesterdayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        
		Calendar calendar2 = Calendar.getInstance();
		calendar2.add(Calendar.DATE, -dayNum);            
        String  lastSevenDayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar2.getTime());
        
        String sql = "SELECT * FROM " + TABLE + " WHERE addTime >= ? and addTime <= ?  and terminal = ? order by addTime";
       Object[] paramObjs = {lastSevenDayDate,yesterdayDate,terminal};
       List<ConsociationCompanyDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   }

}
